package org.spring.springboot.dao;



@Repository
public class ActivityDao extends JdbcBase {
    @Value("${web.upload-path}")
    String path;

    public List<Map<String,Object>> getAdvList(){
        String sql = "select * from system_adv where spot_id=14 order by sort asc";
        return this.getJdbcTemplate().queryForList(sql);
    }
   
   
    public List<Map<String, Object>> sumActivityInfo(Activity act){
        //汇总数据
        String sql="SELECT count(DISTINCT(province) ) as province_count,count(id) as group_count,sum(num) as pcount ," +
                "FORMAT(sum(waste_gw)/1000,3) as waste_sum from activity where 1=1 AND FROM_UNIXTIME(create_time,'%Y')=2020 " +
                "AND submit_time>0 AND (ISNULL(province) || LENGTH(trim(province))>1) ";
        List params=new ArrayList();

        if(this.vertify(act.getTitle())){
            sql+=" AND title LIKE ? ";
            params.add("%"+act.getTitle()+"%");
        }
        if(this.vertify(act.getShibboleth())){
            sql+=" AND shibboleth LIKE ? ";
            params.add("%"+act.getShibboleth()+"%");
        }
        if(this.vertify(act.getCreate_time())){
            sql+=" AND FROM_UNIXTIME(create_time,'%Y-%m')=? ";
            params.add(act.getCreate_time());
        }
        if(this.vertify(act.getProvince())){
            sql+=" AND province =? ";
            params.add(act.getProvince());
        }
        List<Map<String, Object>> mapList = this.getJdbcTemplate().queryForList(sql, params.toArray());
        //查询所有省份的数据
        String height = "3px";
        String width = "2px";
        if(act.getScale()>2){
             height = "15px";
             width = "15px";
        }
        String iconPath = "";
        String type = "";

        iconPath = "/pages/res/xi.png";
        if(act.getScale()==4){
            type = "mark";
        }else{
            type = "all";
        }
        String sql3 ="SELECT id," ;
        if(act.getScale()==1){
            sql3 +=  " (select latitude from system_area where area_name=activity.province and area_deep=1) latitude," +
                    "(select longitude from system_area where area_name=activity.province and area_deep=1) longitude," ;
        }else if (act.getScale()==2){
            sql3 +=  " (select latitude from system_area where area_name=activity.city and area_deep=2) latitude,"+
                    "(select longitude from system_area where area_name=activity.city and area_deep=2) longitude," ;
        }else if(act.getScale()==4){
            sql3+=" address, latitude,longitude, ";
        } else{
            sql3 +="latitude,longitude,";
        }
         sql3 +="province," +
                "city," +
                "district," +
                "count(1) conts," +
                "'"+height+"' height," +
                "'"+iconPath+"' iconPath," +
                "'"+type+"' `type`," +
                "'"+width+"' width " +
                "FROM activity  WHERE 1=1  AND FROM_UNIXTIME(create_time,'%Y')=2020 AND (ISNULL(province) || LENGTH(trim(province))>1) ";

        sql3 +=" AND submit_time>0 ";
        if(this.vertify(act.getTitle())){
            sql3+=" AND title LIKE ? ";
        }
        if(this.vertify(act.getShibboleth())){
            sql3+=" AND shibboleth LIKE ? ";
        }
        if(this.vertify(act.getCreate_time())){
            sql3+=" AND FROM_UNIXTIME(create_time,'%Y-%m')=? ";
        }
        if(this.vertify(act.getProvince())){
            sql3+=" AND province =? ";
        }

//        if(act.getScale()==4){
//            sql3+=" AND (select latitude from system_area where area_name=activity.city and area_deep=2) IS NOT NULL " +
//                    "AND (select longitude from system_area where area_name=activity.city and area_deep=2) IS NOT " +
//                    "NULL  ";
//        }

        if(act.getScale()==1){
            sql3 +=" group by province";
        }else if (act.getScale()==2){
            sql3 +=" group by city";
        }else if (act.getScale()==3){
            sql3 +=" group by district";
        }else{
            //根据经纬度附近100条
//            sql3 +=" group by id ";
            sql3 +="group by id ORDER BY CONVERT((2 * 6378.137* ASIN(SQRT(POW(SIN(PI()*("+act.getLongitude()+ "-longitude)/360),2)+" +
                    "COS(PI()*"+act.getLatitude()+"/180)* COS(latitude * PI()/180)*POW(SIN(PI()*("+act.getLatitude()+"-latitude)/360),2)))),DECIMAL(10,1)) ASC LIMIT 0,100";
//                    sql +=" AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= FROM_UNIXTIME(create_time) group by id";
        }

            List<Map<String,Object>> list = this.getJdbcTemplate().queryForList(sql3, params.toArray());

            if(act.getScale()<4){
                for (Map<String,Object> map :list){
                    //定义地图标记点lable样式
                    Map<String,Object> lable = new HashMap<>();
                    lable.put("anchorX",-10);
                    lable.put("anchorY",-5);
                    lable.put("padding",3);
                    lable.put("bgColor","#36a1f3");
                    lable.put("borderRadius",5);
                    lable.put("color","#ffffff");
                    if(act.getScale()==1){
                        lable.put("content"," "+map.get("province")+":"+map.get("conts")+" ");
                    }else if (act.getScale()==2){
                        lable.put("content",map.get("city")+":"+map.get("conts"));
                    }else if (act.getScale()==3){
                        lable.put("content",map.get("district")+":"+map.get("conts"));
                    }
                    map.put("label",lable);
                }
        }

        Map<String, Double> centerPoint = GPSUtil.getCenterPoint(list);

        mapList.get(0).put("location",centerPoint);
        mapList.get(0).put("actList",list);
        return mapList;
    }
	
	
	  public Map<String, Object> findActivity(Activity activity){
        Map maps=new HashMap();
        String sql="SELECT a.*,COUNT(nsd.id) AS 'number' FROM activity a INNER JOIN net_stand_dianzan nsd \n" +
                "ON a.id=nsd.activity_id  GROUP BY nsd.activity_id ORDER BY number DESC ";

        String countSql="SELECT count(*) AS 'count' FROM (SELECT count(*) FROM activity a \n" +
                "INNER JOIN net_stand_dianzan nsd \n" +
                "ON a.id=nsd.activity_id GROUP BY nsd.activity_id LIMIT 0,100) b";
        Double aDouble = this.getJdbcTemplate().queryForObject(countSql, Double.class);
        activity.setPageSize(10);
        if((activity.getPages() - 1) * activity.getPageSize()>90){
            List<Map<String,Object>> map=new ArrayList<>();
            maps.put("list",map);
            maps.put("pages",activity.getPages());
            maps.put("count",Math.ceil(aDouble/10));
            return maps;
        }else{
            sql +=  " LIMIT " + (activity.getPages() - 1) * activity.getPageSize() + ", " + activity.getPageSize();//翻页
            List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(sql);
            maps.put("list",list);
            maps.put("pages",activity.getPages());
            maps.put("count",Math.ceil(aDouble/10));
            return maps;
        }
    }


    public List<Map<String, Object>> sumActivityInfo(Activity act){
        //汇总数据
        String sql="SELECT count(DISTINCT(province) ) as province_count,count(id) as group_count,sum(num) as pcount ," +
                "FORMAT(sum(waste_gw)/1000,3) as waste_sum from activity where 1=1 AND FROM_UNIXTIME(create_time,'%Y')=2020 " +
                "AND submit_time>0 AND (ISNULL(province) || LENGTH(trim(province))>1) ";
        List params=new ArrayList();

        if(this.vertify(act.getTitle())){
            sql+=" AND title LIKE ? ";
            params.add("%"+act.getTitle()+"%");
        }
        if(this.vertify(act.getShibboleth())){
            sql+=" AND shibboleth LIKE ? ";
            params.add("%"+act.getShibboleth()+"%");
        }
        if(this.vertify(act.getCreate_time())){
            sql+=" AND FROM_UNIXTIME(create_time,'%Y-%m')=? ";
            params.add(act.getCreate_time());
        }
        if(this.vertify(act.getProvince())){
            sql+=" AND province =? ";
            params.add(act.getProvince());
        }
        List<Map<String, Object>> mapList = this.getJdbcTemplate().queryForList(sql, params.toArray());
        //查询所有省份的数据
        String height = "3px";
        String width = "2px";
        if(act.getScale()>2){
             height = "15px";
             width = "15px";
        }
        String iconPath = "";
        String type = "";

        iconPath = "/pages/res/xi.png";
        if(act.getScale()==4){
            type = "mark";
        }else{
            type = "all";
        }
        String sql3 ="SELECT id," ;
        if(act.getScale()==1){
            sql3 +=  " (select latitude from system_area where area_name=activity.province and area_deep=1) latitude," +
                    "(select longitude from system_area where area_name=activity.province and area_deep=1) longitude," ;
        }else if (act.getScale()==2){
            sql3 +=  " (select latitude from system_area where area_name=activity.city and area_deep=2) latitude,"+
                    "(select longitude from system_area where area_name=activity.city and area_deep=2) longitude," ;
        }else if(act.getScale()==4){
            sql3+=" address, latitude,longitude, ";
        } else{
            sql3 +="latitude,longitude,";
        }
         sql3 +="province," +
                "city," +
                "district," +
                "count(1) conts," +
                "'"+height+"' height," +
                "'"+iconPath+"' iconPath," +
                "'"+type+"' `type`," +
                "'"+width+"' width " +
                "FROM activity  WHERE 1=1  AND FROM_UNIXTIME(create_time,'%Y')=2020 AND (ISNULL(province) || LENGTH(trim(province))>1) ";

        sql3 +=" AND submit_time>0 ";
        if(this.vertify(act.getTitle())){
            sql3+=" AND title LIKE ? ";
        }
        if(this.vertify(act.getShibboleth())){
            sql3+=" AND shibboleth LIKE ? ";
        }
        if(this.vertify(act.getCreate_time())){
            sql3+=" AND FROM_UNIXTIME(create_time,'%Y-%m')=? ";
        }
        if(this.vertify(act.getProvince())){
            sql3+=" AND province =? ";
        }

//        if(act.getScale()==4){
//            sql3+=" AND (select latitude from system_area where area_name=activity.city and area_deep=2) IS NOT NULL " +
//                    "AND (select longitude from system_area where area_name=activity.city and area_deep=2) IS NOT " +
//                    "NULL  ";
//        }

        if(act.getScale()==1){
            sql3 +=" group by province";
        }else if (act.getScale()==2){
            sql3 +=" group by city";
        }else if (act.getScale()==3){
            sql3 +=" group by district";
        }else{
            //根据经纬度附近100条
//            sql3 +=" group by id ";
            sql3 +="group by id ORDER BY CONVERT((2 * 6378.137* ASIN(SQRT(POW(SIN(PI()*("+act.getLongitude()+ "-longitude)/360),2)+" +
                    "COS(PI()*"+act.getLatitude()+"/180)* COS(latitude * PI()/180)*POW(SIN(PI()*("+act.getLatitude()+"-latitude)/360),2)))),DECIMAL(10,1)) ASC LIMIT 0,100";
//                    sql +=" AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= FROM_UNIXTIME(create_time) group by id";
        }

            List<Map<String,Object>> list = this.getJdbcTemplate().queryForList(sql3, params.toArray());

            if(act.getScale()<4){
                for (Map<String,Object> map :list){
                    //定义地图标记点lable样式
                    Map<String,Object> lable = new HashMap<>();
                    lable.put("anchorX",-10);
                    lable.put("anchorY",-5);
                    lable.put("padding",3);
                    lable.put("bgColor","#36a1f3");
                    lable.put("borderRadius",5);
                    lable.put("color","#ffffff");
                    if(act.getScale()==1){
                        lable.put("content"," "+map.get("province")+":"+map.get("conts")+" ");
                    }else if (act.getScale()==2){
                        lable.put("content",map.get("city")+":"+map.get("conts"));
                    }else if (act.getScale()==3){
                        lable.put("content",map.get("district")+":"+map.get("conts"));
                    }
                    map.put("label",lable);
                }
        }

        Map<String, Double> centerPoint = GPSUtil.getCenterPoint(list);

        mapList.get(0).put("location",centerPoint);
        mapList.get(0).put("actList",list);
        return mapList;
    }
	
	    public List<Map<String,Object>> provinceList (){
        String sql="SELECT count(id) as provincecount,province FROM activity WHERE submit_time>0 AND DATE_FORMAT(start_time,'%Y-%m-%d')>='2020-04-15' group by province";
        return this.getJdbcTemplate().queryForList(sql,new Object[]{});
    }
    public List<Map<String,Object>> countActivity (){
        String sql="SELECT count(id) as actcount,DATE_FORMAT(start_time,'%Y-%m') as start_time1 FROM activity WHERE " +
                "submit_time>0 AND DATE_FORMAT(start_time,'%Y-%m-%d')>='2020-04-15' group by start_time1 ORDER BY start_time1 DESC ";
        return this.getJdbcTemplate().queryForList(sql,new Object[]{});
    }
    public List<Map<String,Object>> countOrg (){
        String sql="SELECT count(id) as actcount,shibboleth FROM activity WHERE submit_time>0 AND DATE_FORMAT(start_time,'%Y-%m-%d')>='2020-04-15' and shibboleth " +
                "!='undefined' and  shibboleth !=''  group by shibboleth";
        return this.getJdbcTemplate().queryForList(sql,new Object[]{});
    }

    public List<Map<String,Object>> activityList (){
        String sql="SELECT title FROM activity  ORDER BY FROM_UNIXTIME(create_time,'%Y-%m-%d') DESC ";
        return this.getJdbcTemplate().queryForList(sql,new Object[]{});
    }

	 public Map<String, Object> searchActivityList(Activity act){
     Map<String,Object> map = new HashMap<String, Object>();
     List params=new ArrayList();
        String sql ="SELECT * FROM activity  WHERE 1=1 AND submit_time>0 AND DATE_FORMAT(start_time,'%Y-%m-%d') >='2020-04-15'  ";
        String sql2 ="SELECT count(id) FROM activity  WHERE 1=1 AND submit_time>0 AND DATE_FORMAT(start_time,'%Y-%m-%d') >='2020-04-15'";
     String sql3 =" ";

     if(this.vertify(act.getTitle())){
         sql3+=" AND title LIKE ? ";
         params.add("%"+act.getTitle()+"%");
     }
     if(this.vertify(act.getShibboleth())){
         sql3+=" AND shibboleth LIKE ? ";
         params.add("%"+act.getShibboleth()+"%");
     }
     if(this.vertify(act.getStart_time())){
         sql3+=" AND DATE_FORMAT(start_time,'%Y-%m-%d') Like ? ";
         params.add("%"+act.getStart_time()+"%");
     }
     if(this.vertify(act.getProvince())){
         sql3+=" AND province =? ";
         params.add(act.getProvince());
     }
     sql = sql + sql3;
     sql2 = sql2 + sql3;
            //根据经纬度附近50条
     sql +=" group by id ORDER BY id DESC";
//                    sql +=" AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= FROM_UNIXTIME(create_time) group by id";
     act.setPageSize(5);
     sql +=  " LIMIT " + (act.getPages() - 1) * act.getPageSize() + ", " + act.getPageSize();//翻页
     List<Map<String,Object>> list = this.getJdbcTemplate().queryForList(sql, params.toArray());

     Double count =this.getJdbcTemplate().queryForObject(sql2, params.toArray(), Double.class);
     double pageCount=count/5;
     System.out.println(pageCount+"==========================");
     map.put("pageCount",Math.ceil(pageCount));
     map.put("actCount",count);
     map.put("pages",act.getPages());
     map.put("list",list);
        return map;
    }

    public Map<String, Object> findActivity(Activity activity){
        Map maps=new HashMap();
        String sql="SELECT a.*,COUNT(nsd.id) AS 'number' FROM activity a INNER JOIN net_stand_dianzan nsd \n" +
                "ON a.id=nsd.activity_id  GROUP BY nsd.activity_id ORDER BY number DESC ";

        String countSql="SELECT count(*) AS 'count' FROM (SELECT count(*) FROM activity a \n" +
                "INNER JOIN net_stand_dianzan nsd \n" +
                "ON a.id=nsd.activity_id GROUP BY nsd.activity_id LIMIT 0,100) b";
        Double aDouble = this.getJdbcTemplate().queryForObject(countSql, Double.class);
        activity.setPageSize(10);
        if((activity.getPages() - 1) * activity.getPageSize()>90){
            List<Map<String,Object>> map=new ArrayList<>();
            maps.put("list",map);
            maps.put("pages",activity.getPages());
            maps.put("count",Math.ceil(aDouble/10));
            return maps;
        }else{
            sql +=  " LIMIT " + (activity.getPages() - 1) * activity.getPageSize() + ", " + activity.getPageSize();//翻页
            List<Map<String, Object>> list = this.getJdbcTemplate().queryForList(sql);
            maps.put("list",list);
            maps.put("pages",activity.getPages());
            maps.put("count",Math.ceil(aDouble/10));
            return maps;
        }
    }


	    public List<Map<String, Object>> findActivityInfo(Integer id){
        String sql="SELECT a.id,a.title,FROM_UNIXTIME(a.start_time,'%Y-%m-%d') AS 'start_time',(SELECT org_name FROM organization o WHERE o.id=a.org_id) AS 'orgName',a.longitude,a.latitude,a.relation,a.phone,a.content,a.city,a.district,a.address,a.province FROM activitys a\n" +
                "WHERE a.id=?";
        List<Map<String, Object>> mapList = this.getJdbcTemplate().queryForList(sql, new Object[]{id});

        String sql1="SELECT a.id,a.nick_name,a.cover FROM activitys_member am\n" +
                "INNER JOIN account a\n" +
                "ON am.account_id=a.id\n" +
                "WHERE am.activitys_id=?";
        List<Map<String, Object>> mapList1 = this.getJdbcTemplate().queryForList(sql1, new Object[]{id});

            mapList.get(0).put("accountList",mapList1);
            return mapList;
    }

   

    }




